﻿Imports System.Data.SqlClient

Public Class KlasifikasiDb
    Dim connectionString As String

    Public Sub New(ByVal connectionString As String)
        Me.connectionString = connectionString
    End Sub

    Public Function isExist(ByVal IdKlasifikasi As String, ByVal KodeKlasifikasi As String) As Boolean
        Dim connection As New SqlConnection(connectionString)
        Dim command1 As SqlCommand
        Dim query1 As String = "select count(*) from TB_MST_KLASIFIKASI where kode_klasifikasi = @KodeKlasifikasi"
        Dim count1 As Integer = 0
        Dim command2 As SqlCommand
        Dim query2 As String = "select count(*) from TB_MST_KLASIFIKASI where id_klasifikasi = @IdKlasifikasi and kode_klasifikasi = @KodeKlasifikasi"
        Dim count2 As Integer = 0

        Try
            connection.Open()
            command1 = New SqlCommand(query1, connection)
            command1.Parameters.AddWithValue("@KodeKlasifikasi", KodeKlasifikasi)
            count1 = CInt(command1.ExecuteScalar())

            command2 = New SqlCommand(query2, connection)
            command2.Parameters.AddWithValue("@IdKlasifikasi", CInt(IdKlasifikasi))
            command2.Parameters.AddWithValue("@KodeKlasifikasi", KodeKlasifikasi)
            count2 = CInt(command2.ExecuteScalar())

        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
        End Try

        If IdKlasifikasi = "0" And count1 > 0 Then
            Return True
        ElseIf IdKlasifikasi <> "0" And count1 > 0 And count2 = 0 Then
            Return True
        ElseIf IdKlasifikasi <> "0" And count2 = 1 Then
            Return False
        Else
            Return False
        End If

    End Function

    Public Sub insert(ByVal KodeKlasifikasi As String, ByVal Deskripsi As String, ByVal IdJenisDokumen As Integer, ByVal NilaiGuna As String, _
                      ByVal NomorSE As String, ByVal TanggalSE As String, ByVal IsActive As String)
        Dim connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(" insert into TB_MST_KLASIFIKASI (kode_klasifikasi, deskripsi, id_jenis_dok, nilai_guna, nomor_se, tgl_se, is_active, tgl_update) " +
                                      " values (@KodeKlasifikasi, @Deskripsi, @IdJenisDokumen, @NilaiGuna, @NomorSE, @TanggalSE, @IsActive, CURRENT_TIMESTAMP)", connection)
        Try
            connection.Open()
            command.Parameters.AddWithValue("@KodeKlasifikasi", KodeKlasifikasi)
            command.Parameters.AddWithValue("@Deskripsi", Deskripsi)
            command.Parameters.AddWithValue("@IdJenisDokumen", IdJenisDokumen)
            command.Parameters.AddWithValue("@NilaiGuna", NilaiGuna)
            command.Parameters.AddWithValue("@NomorSE", NomorSE)
            command.Parameters.AddWithValue("@TanggalSE", TanggalSE)
            command.Parameters.AddWithValue("@IsActive", IsActive)
            command.ExecuteNonQuery()
        Catch ex As SqlException
            Throw ex
        Finally
            connection.Close()
        End Try
    End Sub

    Public Sub update(ByVal IdKlasifikasi As String, ByVal KodeKlasifikasi As String, ByVal Deskripsi As String, ByVal IdJenisDokumen As Integer, ByVal NilaiGuna As String, _
                      ByVal NomorSE As String, ByVal TanggalSE As String, ByVal IsActive As String)
        Dim connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(" update TB_MST_KLASIFIKASI set kode_klasifikasi=@KodeKlasifikasi, deskripsi=@Deskripsi, id_jenis_dok=@IdJenisDokumen, nilai_guna=@NilaiGuna, " +
                                      " nomor_se=@NomorSE, tgl_se=convert(datetime, @TanggalSE, 103), is_active=@IsActive, tgl_update=current_timestamp where id_klasifikasi = @IdKlasifikasi", connection)
        Try
            connection.Open()
            command.Parameters.AddWithValue("@KodeKlasifikasi", KodeKlasifikasi)
            command.Parameters.AddWithValue("@Deskripsi", Deskripsi)
            command.Parameters.AddWithValue("@IdJenisDokumen", IdJenisDokumen)
            command.Parameters.AddWithValue("@NilaiGuna", NilaiGuna)
            command.Parameters.AddWithValue("@NomorSE", NomorSE)
            command.Parameters.AddWithValue("@TanggalSE", TanggalSE)
            command.Parameters.AddWithValue("@IsActive", IsActive)
            command.Parameters.AddWithValue("@IdKlasifikasi", IdKlasifikasi)
            command.ExecuteNonQuery()
        Catch ex As SqlException
            Throw ex
        Finally
            connection.Close()
        End Try
    End Sub
End Class
